package com.xiaorong.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelUtils {
	
	private ExcelUtils(){
	}

	/**
	 * 读取excel的单元格
	 * @param sheet
	 * @param x
	 * @param y
	 * @return
	 */
	public static String readValue(Sheet sheet, int y, int x) {
		String value = "";
		if (sheet == null) {
			return null;
		}
		Row row = sheet.getRow(x);
		if(row == null){
			return null;
		}
		Cell cell = row.getCell(y);
		if(cell == null){
			return null;
		}
		int rowType = cell.getCellType();
		if (rowType == Cell.CELL_TYPE_STRING) {
			value = cell.getStringCellValue();
		} else if (rowType == Cell.CELL_TYPE_NUMERIC) {
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				String date = sdf.format(
						HSSFDateUtil.getJavaDate(cell.getNumericCellValue()))
						.toString();
				value = date.toString();

			} else {
				Number v = cell.getNumericCellValue();
				double va = Double.valueOf(v.toString());
				//int val = (int) va;
				//value = String.valueOf(val);
				value = String.valueOf(va);

			}
		}
		return value;
		
	}
	
	/**
	 * 读取excel的单元格�?
	 * @param cell
	 * @return
	 */
	public static String readValue(Cell cell) {
		String value = "";
		if (cell == null) {
			return "NULL";
		}
		int rowType = cell.getCellType();
		if (rowType == Cell.CELL_TYPE_STRING) {
			value = cell.getStringCellValue();
		} else if (rowType == Cell.CELL_TYPE_NUMERIC) {
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				String date = sdf.format(
						HSSFDateUtil.getJavaDate(cell.getNumericCellValue()))
						.toString();
				value = date.toString();

			} else {
				Number v = cell.getNumericCellValue();
				// 如果是身份证(第三�?（因为身份证全是数字）则把小数部分去�?
				if (cell.getColumnIndex() == 3) {
					double va = Double.valueOf(v.toString());
					int val = (int) va;
					value = String.valueOf(val);

				} else {
					value = v.toString();
				}

			}
		}
		return value;
	}
	
	public static Sheet readExcel(String fileName) throws IOException{
		// 创建对Excel工作薄文件的引用
		FileInputStream in = new FileInputStream(fileName);
		//Excel2003
		Workbook wb = new HSSFWorkbook(in);
		// 创建对工作表的引�?
		return wb.getSheetAt(0);
	}
	
	/**
	 * 导出excel
	 * @param filename
	 * @param location
	 * @param head
	 * @param valueList
	 * @return
	 * @throws IOException
	 */
	public static HSSFWorkbook exportExcel2FilePath(String filename, String location, String[] head, List<List<String>> valueList) throws IOException {  
        // 第一步，创建�?��webbook，对应一个Excel文件  
        HSSFWorkbook wb = new HSSFWorkbook();  
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
        wb.createSheet("sheet1");
        HSSFSheet sheet = wb.getSheetAt(0);
    	// 第三步，创建单元格，并设置�?表头 设置表头居中  
        HSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建�?��居中格式  
        int lastRowNum = sheet.getLastRowNum();
        HSSFRow row = sheet.createRow(lastRowNum);
        HSSFCell cell = null;
        
        if(head != null){
    		
        	for(int i=0; i<head.length; i++){
        		cell = row.createCell(i);
        		cell.setCellValue(head[i]);  
        		cell.setCellStyle(style);  
        	}
        }
        
        Integer cyclecell = null;
        
        if(valueList != null){
        	// 第四步，创建单元格，并设置�?  
        	for (int i = 0; i < valueList.size(); i++) {
        		row = sheet.createRow(++lastRowNum);
        		
        		int temp = 0;
        		for(int j = 0; j < valueList.get(i).size(); j ++){
        			//设置那列
        			if(cyclecell != null){
        				cell = row.createCell(temp ++); 
        			}else{
        				cell = row.createCell(j);
        			}
        			
        			if(valueList.get(i).get(j) != null){
        				cell.setCellValue(valueList.get(i).get(j));
        			}else{
        				cell.setCellValue("");
        			}
        			cell.setCellStyle(style);
        			
        		}
        		cyclecell = null;
        	}
        }
        // 第六步，将文件存到指定位�? 
    	FileOutputStream os = new FileOutputStream(new File(location+filename));
        wb.write(os);
        os.flush();
        os.close();
        
        return wb;
   }  


}